# -*- coding:utf-8 -*—
import math
import os
import time

import pandas as pd
from pandas import DataFrame

Anhui_aste = "Anhui_aste"
Beijing_as = "Beijing_as"
Chongqing_ = "Chongqing_"
Fujian_ast = "Fujian_ast"
Gansu_aste = "Gansu_aste"
Guangdong_ = "Guangdong_"
Guangxi_as = "Guangxi_as"
Guizhou_as = "Guizhou_as"
Hainan_ast = "Hainan_ast"
Hebei_aste = "Hebei_aste"
Heilongjia = "Heilongjia"
Henan_aste = "Henan_aste"
Hubei_aste = "Hubei_aste"
Hunan_aste = "Hunan_aste"
Jiangsu_as = "Jiangsu_as"
Jiangxi_as = "Jiangxi_as"
Jilin = "Jilin"
Liaoning = "Liaoning"
Neimenggu = "Neimenggu"
Ningxia_as = "Ningxia_as"
Qinghai = "Qinghai"
Shandong_a = "Shandong_a"
Shanghai_a = "Shanghai_a"
Shanxi2_as = "Shanxi2_as"
Shanxi_ast = "Shanxi_ast"
Sichuan_as = "Sichuan_as"
Taiwan_ast = "Taiwan_ast"
Tianjin_as = "Tianjin_as"
Xinjiang_a = "Xinjiang_a"
Xizang_ast = "Xizang_ast"
Yunnan_ast = "Yunnan_ast"
Zhejiang_a = "Zhejiang_a"


targeField="height"

def readExcel(excelPath):
    df: DataFrame = pd.read_excel(io=excelPath)
    return df

def exportExcel(open_path,df):
    filename = os.path.basename(open_path)
    # print(os.path.basename(path))

    stem, suffix = os.path.splitext(filename)
    # print(stem)  # test
    # print(suffix)  # .py
    exportPath = os.path.split(os.path.abspath(open_path))[0]
    now_time = time.strftime('%Y-%m-%d-%H-%M-%S', time.localtime())
    outname = "./"+stem+"更新excel列" + now_time + ".xlsx"
    # print(outname)
    outputPath = exportPath +outname
    # outputPath = exportPath +'./excel转txt1.txt'
    # df.to_excel(outputPath, sep='\t', index=None)
    df.to_excel(outputPath,index = False,encoding="utf-8")
    print('导出excel完成')

def mergeColumn(sourcePath):
    dfsources = readExcel(sourcePath)
    # print(dfsources)
    for index, row in dfsources.iterrows():
        # print(index)  # 输出每行的索引值
        if (math.isnan(row[Anhui_aste]) == False):
            # row[targeField] = row[Anhui_aste]
            dfsources.loc[index:, targeField]=row[Anhui_aste]
            continue
        if (math.isnan(row[Beijing_as]) == False):
            dfsources.loc[index:, targeField] = row[Beijing_as]
            continue
        if (math.isnan(row[Chongqing_]) == False):
            dfsources.loc[index:, targeField] = row[Chongqing_]
            continue
        if (math.isnan(row[Fujian_ast]) == False):
            dfsources.loc[index:, targeField] = row[Fujian_ast]
            continue
        if (math.isnan(row[Gansu_aste]) == False):
            dfsources.loc[index:, targeField] = row[Gansu_aste]
            continue
        if (math.isnan(row[Guangdong_]) == False):
            dfsources.loc[index:, targeField] = row[Guangdong_]
            continue
        if (math.isnan(row[Guangxi_as]) == False):
            dfsources.loc[index:, targeField] = row[Guangxi_as]
            continue
        if (math.isnan(row[Guizhou_as]) == False):
            dfsources.loc[index:, targeField] = row[Guizhou_as]
            continue
        if (math.isnan(row[Hainan_ast]) == False):
            dfsources.loc[index:, targeField] = row[Hainan_ast]
            continue
        if (math.isnan(row[Hebei_aste]) == False):
            dfsources.loc[index:, targeField] = row[Hebei_aste]
            continue
        if (math.isnan(row[Heilongjia]) == False):
            dfsources.loc[index:, targeField] = row[Heilongjia]
            continue
        if (math.isnan(row[Henan_aste]) == False):
            dfsources.loc[index:, targeField] = row[Henan_aste]
            continue
        if (math.isnan(row[Hubei_aste]) == False):
            dfsources.loc[index:, targeField] = row[Hubei_aste]
            continue
        if (math.isnan(row[Hunan_aste]) == False):
            dfsources.loc[index:, targeField] = row[Hunan_aste]
            continue
        if (math.isnan(row[Jiangsu_as]) == False):
            dfsources.loc[index:, targeField] = row[Jiangsu_as]
            continue
        if (math.isnan(row[Jiangxi_as]) == False):
            dfsources.loc[index:, targeField] = row[Jiangxi_as]
            continue
        if (math.isnan(row[Jilin]) == False):
            dfsources.loc[index:, targeField] = row[Jilin]
            continue
        if (math.isnan(row[Liaoning]) == False):
            dfsources.loc[index:, targeField] = row[Liaoning]
            continue
        if (math.isnan(row[Neimenggu]) == False):
            dfsources.loc[index:, targeField] = row[Neimenggu]
            continue
        if (math.isnan(row[Ningxia_as]) == False):
            dfsources.loc[index:, targeField] = row[Ningxia_as]
            continue
        if (math.isnan(row[Qinghai]) == False):
            dfsources.loc[index:, targeField] = row[Qinghai]
            continue
        if (math.isnan(row[Shandong_a]) == False):
            dfsources.loc[index:, targeField] = row[Shandong_a]
            continue
        if (math.isnan(row[Shanghai_a]) == False):
            dfsources.loc[index:, targeField] = row[Shanghai_a]
            continue
        if (math.isnan(row[Shanxi2_as]) == False):
            dfsources.loc[index:, targeField] = row[Shanxi2_as]
            continue
        if (math.isnan(row[Shanxi_ast]) == False):
            dfsources.loc[index:, targeField] = row[Shanxi_ast]
            continue
        if (math.isnan(row[Sichuan_as]) == False):
            dfsources.loc[index:, targeField] = row[Sichuan_as]
            continue
        if (math.isnan(row[Taiwan_ast]) == False):
            dfsources.loc[index:, targeField] = row[Taiwan_ast]
            continue
        if (math.isnan(row[Tianjin_as]) == False):
            dfsources.loc[index:, targeField] = row[Tianjin_as]
            continue
        if (math.isnan(row[Xinjiang_a]) == False):
            dfsources.loc[index:, targeField] = row[Xinjiang_a]
            continue
        if (math.isnan(row[Xizang_ast]) == False):
            dfsources.loc[index:, targeField] = row[Xizang_ast]
            continue
        if (math.isnan(row[Yunnan_ast]) == False):
            dfsources.loc[index:, targeField] = row[Yunnan_ast]
            continue
        if (math.isnan(row[Zhejiang_a]) == False):
            dfsources.loc[index:, targeField] = row[Zhejiang_a]
            continue
    exportExcel(sourcePath, dfsources)


if __name__ == '__main__':
    sourcePath = r"F:\data\1.dem\12.5m\提取数据\水质站2022.12.20.xlsx"
    # sourceIndex = '编号'
    # sourceValueColoumn = '设计地面标高(m)'
    mergeColumn(sourcePath)
